﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using CommunitySystem.BO;
using CommunitySystemModel;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using org.in2bits.MyXls;

namespace CommunitySystem
{
    public class ZhaoLuBO
    {
        #region 职位申请
        /// <summary>
        /// 获得登录人可见的申报的职位--yy修改
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public ObservableCollection<ZhaoLuZhiWeiModel> GetZhaoLuZhiWei(string OrgID, int ID, int start, int end, int status, int year, string islocked)
        {
            ObservableCollection<ZhaoLuZhiWeiModel> rights = new ObservableCollection<ZhaoLuZhiWeiModel>();
            string sql = "";
            if (ID == 0)
            {
                if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                {
                    sql =
                        @"select top " + (end - start) + @" ROW_NUMBER() OVER(ORDER BY p.id ) Sec, p.*,o.Name OrgName from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where p.ID not   in   
(select   top " + start + @" ID from ZhaoLuZhiWei ) and
                     (o.ParentID in (" + OrgID + ") or o.ID in (" + OrgID + ")) and p.year = " + year;
                    //if (OrgID == 80 || OrgID == 81 || OrgID == 82 || OrgID == 83 || OrgID == 84)
                    //{
                    //    sql += " and o.parentid = " + OrgID;
                    //}
                    //if (OrgID != 80 && OrgID != 81 && OrgID != 82 && OrgID != 83 && OrgID != 84 && OrgID != 79 && OrgID != 0)
                    //{
                    //    sql += " and o.id = " + OrgID;
                    //}
                    if (status != -1)
                    {
                        sql += " and p.status = " + status;
                    }
                    sql += " and p.IsLocked = '" + islocked + "'";
                }
                else//admin登录的
                {
                    sql = @"select top " + (end - start) + @" ROW_NUMBER() OVER(ORDER BY p.id ) Sec, p.*,o.Name OrgName from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID 
where p.ID not   in   
(select   top " + start + @" ID from ZhaoLuZhiWei )  and p.year = " + year;
                    if (status != -1)
                    {
                        sql += " and p.status = " + status;
                    }
                    sql += " and p.IsLocked = '" + islocked + "'";
                    sql += " order by p.ID";
                }
            }

            else//修改单个信息的时候用
            {
                sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY p.id ) Sec, p.*,o.Name OrgName from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where p.ID = {0}", ID);
                if (status != -1)
                {
                    sql += " and p.status = " + status;
                }
                sql += " and p.IsLocked = '" + islocked + "'";
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLuZhiWeiModel.FillEntity(dr));
            }
            return rights;
        }

        public bool DeteleZhaoLuZhiWei(string ID)
        {
            string sql = "delete from ZhaoLuZhiWei where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 确定保存
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool SaveZhaoLuZhiWei(string ID)
        {
            string sql = "update dbo.ZhaoLuZhiWei set Status = 1 where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 查看职位未审核的有多少条--yy修改
        /// </summary>
        /// <param name="OrgID"></param>
        /// <returns></returns> 
        public string ChaKanShiFouDouShenHeZhaoLuZhiWei(string OrgID, int year, string islocked)
        {
            string sql = "";
            if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
            {
                sql =
                    @"select count(p.[ID]) Count from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where  p.status =1 and
                     (o.ParentID in (" +
                    OrgID + ") or o.ID in (" + OrgID + ")) and p.year = " + year;
            }
            else
            {
                sql =
                    @"select count(p.[ID]) Count from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where p.status =1 and p.year = " + year;
            }
            sql += " and p.IsLocked = '" + islocked + "'";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            else return null;
        }

        /// <summary>
        /// yy修改
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="ID"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int GetZhaoLuZhiWeiCount(string OrgID, int ID, int status, int year, string islocked)
        {
            string sql = "";
            if (ID == 0)
            {
                if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                {
                    sql =
                        @"select count(p.[ID])/20 + 1 Count from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where 1=1 and p.year = " + year + @" and
                     (o.ParentID in (" + OrgID + ") or o.ID in (" + OrgID + ")) ";
                    //if (OrgID == 80 || OrgID == 81 || OrgID == 82 || OrgID == 83 || OrgID == 84)
                    //{
                    //    sql += " and o.parentid = " + OrgID;
                    //}
                    //if (OrgID != 80 && OrgID != 81 && OrgID != 82 && OrgID != 83 && OrgID != 84 && OrgID != 79 && OrgID != 0)
                    //{
                    //    sql += " and o.id = " + OrgID;
                    //}
                    if (status != -1)
                    {
                        sql += " and p.status = " + status;
                    }
                    sql += " and p.IsLocked = '" + islocked + "'";
                }
                else//admin登录的
                {
                    sql =
                        @"select count(p.[ID])/20 + 1 Count from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where 1=1 and p.year = " +
                        year;
                    if (status != -1)
                    {
                        sql += " and p.status = " + status;
                    }
                    sql += " and p.IsLocked = '" + islocked + "'";
                }
            }

            else//修改单个信息的时候用
            {
                sql = string.Format(@"select count(p.[ID])/20 + 1 Count from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where p.ID = {0}", ID);
                if (status != -1)
                {
                    sql += " and p.status = " + status;
                }
                sql += " and p.IsLocked = '" + islocked + "'";
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["Count"]);
        }

        /// <summary>
        /// 通过审核
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool CheckZhaoLuZhiWei(string ID)
        {
            string sql = "update dbo.ZhaoLuZhiWei set Status = 3 where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 退回
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool TuiZhaoLuZhiWei(string ID, string memo)
        {
            string sql = "update dbo.ZhaoLuZhiWei set Status = 2,CheckMemo = '" + memo + "' where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 发布
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool FaBuZhaoLuZhiWei(string ID)
        {
            string sql = "update dbo.ZhaoLuZhiWei set Status = 4 where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 取消审核
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool CancelZhaoLuZhiWei(string ID)
        {
            string sql = "update dbo.ZhaoLuZhiWei set Status = 1 where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool AddZhaoLuZhiWei(ZhaoLuZhiWeiModel item)
        {
            string sql = string.Format(@"INSERT INTO [XuHuiCommunityDB].[dbo].[ZhaoLuZhiWei]
           ([OrgID]
           ,[Department]
           ,[Duty]
           ,[DutyDescription]
           ,[PeopleCount]
           ,[JCGZZDNX]
           ,[ZKDX]
           ,[BenKeZhuanYe]
           ,[YanJiuShengZhuanYe]
           ,[XueLiYaoQiu]
           ,[Political]
           ,[ZYKSKM]
           ,[Other]
           ,[Memo]
           ,[Status]
           ,[CheckMemo]
           ,[XueWeiYaoQiu]
           ,[Year]
           ,[IsLocked],Department_Duty)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,{4}
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,{14}
           ,'{15}'
           ,'{16}'
           ,{17}
           ,'{18}','{19}')", item.OrgID, item.Department, item.Duty, item.DutyDescription, item.PeopleCount, item.JCGZZDNX, item.ZKDX, item.BenKeZhuanYe,
                    item.YanJiuShengZhuanYe, item.XueLiYaoQiu, item.Political, item.ZYKSKM, item.Other, item.Memo, item.Status, item.CheckMemo,
                    item.XueWeiYaoQiu, item.Year, item.IsLocked,item.Department_Duty);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateZhaoLuZhiWei(ZhaoLuZhiWeiModel item)
        {
            string sql = string.Format(@"UPDATE [XuHuiCommunityDB].[dbo].[ZhaoLuZhiWei]
   SET [OrgID] = {0}
      ,[Department] ='{1}'
      ,[Duty] = '{2}'
      ,[DutyDescription] = '{3}'
      ,[PeopleCount] = {4}
      ,[JCGZZDNX] = '{5}'
      ,[ZKDX] = '{6}'
      ,[BenKeZhuanYe] = '{7}'
      ,[YanJiuShengZhuanYe] = '{8}'
      ,[XueLiYaoQiu] = '{9}'
      ,[Political] = '{10}'
      ,[ZYKSKM] = '{11}'
      ,[Other] = '{12}'
      ,[Memo] = '{13}'
      ,[Status] = {14}
      ,[CheckMemo] = '{15}'
      ,[XueWeiYaoQiu] = '{16}'
      ,[Year] = {17}
      ,[IsLocked] = '{18}',Department_Duty = '{19}'
 WHERE ID = {20}", item.OrgID, item.Department, item.Duty, item.DutyDescription, item.PeopleCount, item.JCGZZDNX, item.ZKDX, item.BenKeZhuanYe,
                    item.YanJiuShengZhuanYe, item.XueLiYaoQiu, item.Political, item.ZYKSKM, item.Other, item.Memo, item.Status, item.CheckMemo,
                    item.XueWeiYaoQiu, item.Year, item.IsLocked, item.Department_Duty, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        /// <summary>
        /// 锁定本期工作
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool SuoZhaoLuZhiWei()
        {
            string sql = @"update dbo.ZhaoLuZhiWei set islocked = 1 where islocked = 0;
update ZhaoLuJiHua set IsLocked = 1 where ID in
(select ZhaoLuJiHuaID from ZhaoLuJiHuaDetail1 where ZhaoLuZhiWeiID in
(select ID from ZhaoLuZhiWei where IsLocked = 0)) ";
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 计划录用管理
        public ObservableCollection<ZhaoLuJiHuaModel> GetZhaoLuJiHuaModel(int zhaoluid, string islocked)
        {
            ObservableCollection<ZhaoLuJiHuaModel> rights = new ObservableCollection<ZhaoLuJiHuaModel>();
            string sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY id ) Sec,* from ZhaoLuJiHua  where 1=1 ");
            if (zhaoluid != 0)
            {
                sql += " and year = " + zhaoluid;
            }
            if (!string.IsNullOrEmpty(islocked))
            {
                sql += " and IsLocked = '" + islocked + "'";
            }
            sql += " order by id desc";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLuJiHuaModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<ZhaoLuJiHuaDetailModel> GetZhaoLuJiHuaDetailModel(int id, int year)
        {
            ObservableCollection<ZhaoLuJiHuaDetailModel> rights = new ObservableCollection<ZhaoLuJiHuaDetailModel>();
            string sql = "";
            if (id != 0)
            {
                sql = string.Format(@"SELECT distinct zd.[ID]
      ,[ZhaoLuJiHuaID]
      ,[HDXZBZS_QJJG]
      ,[HDXZBZS_JD]
      ,[HDXZBZS_XZ]
      ,[HDXZBZS_ZF]
      ,[SYRS_QJJG]
      ,[SYRS_JD]
      ,[SYRS_XZ]
      ,[SYRS_ZF]
      ,[JYRS_QJJG]
      ,[JYRS_JD]
      ,[JYRS_XZ]
      ,[JYRS_ZF]
      ,[QBRS_QJJG]
      ,[QBRS_JD]
      ,[QBRS_XZ]
      ,[QBRS_ZF]
      ,[ZS_QJJG]
      ,[ZS_JD]
      ,[ZS_XZ]
      ,[ZS_ZF]
      ,[YXCGB]
      ,[YXJWHGB]
      ,[DXSCG]
      ,[SZ], LiangNian, BX,HDXZBZS_HJ,SYRS_HJ,JYRS_HJ,QBRS_HJ,ZS_HJ
  FROM ZhaoLuZhiWei zw 
  left join ZhaoLuJiHua z on zw.Year = z.Year
  left join ZhaoLuJiHuaDetail zd on zd.ZhaoLuJiHuaID = z.ID where zw.IsLocked = 0 and zw.Status = 4 and z.IsLocked = 0 and 1=1 ", year);
                sql += " and ZhaoLuJiHuaID = " + id;
            }
            else
            {
                sql = string.Format(@"SELECT distinct zd.[ID]
      ,[ZhaoLuJiHuaID]
      ,[HDXZBZS_QJJG]
      ,[HDXZBZS_JD]
      ,[HDXZBZS_XZ]
      ,[HDXZBZS_ZF]
      ,[SYRS_QJJG]
      ,[SYRS_JD]
      ,[SYRS_XZ]
      ,[SYRS_ZF]
      ,[JYRS_QJJG]
      ,[JYRS_JD]
      ,[JYRS_XZ]
      ,[JYRS_ZF]
      ,[QBRS_QJJG]
      ,[QBRS_JD]
      ,[QBRS_XZ]
      ,[QBRS_ZF]
      ,[ZS_QJJG]
      ,[ZS_JD]
      ,[ZS_XZ]
      ,[ZS_ZF]
      ,[YXCGB]
      ,[YXJWHGB]
      ,[DXSCG]
      ,[SZ],case when a.Cou is null then 0 else a.Cou end LiangNian,case when b.Cou is null then 0 else b.Cou end BX
,HDXZBZS_HJ,SYRS_HJ,JYRS_HJ,QBRS_HJ,ZS_HJ
  FROM ZhaoLuZhiWei zw 
  left join ZhaoLuJiHua z on zw.Year = z.Year
  left join ZhaoLuJiHuaDetail zd on zd.ZhaoLuJiHuaID = z.ID
  left join
  (select sum(PeopleCount) Cou,YEAR from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZKDX = '具有两年以上基层工作经历的人员' group by YEAR) a on a.Year = z.Year
  left join
  (select sum(PeopleCount) Cou,YEAR from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZKDX = '不限'  group by YEAR) b on b.Year = z.Year where zw.IsLocked = 0 and zw.Status = 4  ", year);
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLuJiHuaDetailModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<ZhaoLuJiHuaDetail1Model> GetZhaoLuJiHuaDetail1Model(int id, int year)
        {
            ObservableCollection<ZhaoLuJiHuaDetail1Model> rights = new ObservableCollection<ZhaoLuJiHuaDetail1Model>();
            string sql = "";
            if (id != 0)
            {
                sql = string.Format(@" select ROW_NUMBER() OVER(ORDER BY zd.id ) Sec, zd.*,zw.Duty,o.Name OrgName , (o.Name + zw.Duty) as OrgName_Duty,zw.ID ZhaoLuZhiWeiID  from ZhaoLuZhiWei zw
 left join ZhaoLuJiHuaDetail1 zd on zw.ID = zd.ZhaoLuZhiWeiID
 left join ZhaoLuJiHua z on z.ID = zd.ZhaoLuJiHuaID
 left join OrgForHR o on o.ID = zw.OrgID where  zw.Status = 4 and 1=1  ");
                sql += " and ZhaoLuJiHuaID = " + id;
            }
            else
            {
                sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY zd.id ) Sec, zd.ID,zd.ZhaoLuJiHuaID,zw.ID ZhaoLuZhiWeiID, zw.Duty,o.Name OrgName, (o.Name + zw.Duty) as OrgName_Duty,a.Cou LiangNian,b.Cou BX,null YXCGB,null YXJWHGB,null DXSCG,null SanZhi,c.Cou ZF,
d.Cou ZHGL,e.Cou XXGL,f.Cou CWGL,g.Cou JJGL,h.Cou CSJSGL,i.Cou NCJSGL,
j.Cou BSYJS,k.Cou SSJYS,l.Cou SSYJS,m.Cou BKJYS,n.Cou BK,p.Cou DZJYS,q.Cou DZ,
r.Cou BS,s.Cou SS,t.Cou XS,u.Cou WYQ from ZhaoLuZhiWei zw
 left join ZhaoLuJiHuaDetail1 zd on zw.ID = zd.ZhaoLuZhiWeiID
 left join ZhaoLuJiHua z on z.ID = zd.ZhaoLuJiHuaID
 left join OrgForHR o on o.ID = zw.OrgID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZKDX = '具有两年以上基层工作经历的人员' group by YEAR,ID) a on a.Year = zw.Year and zw.ID = a.ID
 left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZKDX = '不限'  group by YEAR,ID) b on b.Year = zw.Year and zw.ID = b.ID
   left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '政法'  group by YEAR,ID) c on c.Year = zw.Year and zw.ID = c.ID
    left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '综合管理'  group by YEAR,ID) d on d.Year = zw.Year and zw.ID = d.ID
    left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '信息管理'  group by YEAR,ID) e on e.Year = zw.Year and zw.ID = e.ID
   left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '财务管理'  group by YEAR,ID) f on f.Year = zw.Year and zw.ID = f.ID
   left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '经济管理'  group by YEAR,ID) g on g.Year = zw.Year and zw.ID = g.ID
   left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '城市建设管理'  group by YEAR,ID) h on h.Year = zw.Year and zw.ID = h.ID
   left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '农村建设管理'  group by YEAR,ID) i on i.Year = zw.Year and zw.ID = i.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '博士研究生'  group by YEAR,ID) j on j.Year = zw.Year and zw.ID = j.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '硕士及以上'  group by YEAR,ID) k on k.Year = zw.Year and zw.ID = k.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '硕士研究生'  group by YEAR,ID) l on l.Year = zw.Year and zw.ID = l.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '本科及以上'  group by YEAR,ID) m on m.Year = zw.Year and zw.ID = m.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '本科'  group by YEAR,ID) n on n.Year = zw.Year and zw.ID = n.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '大专及以上'  group by YEAR,ID) p on p.Year = zw.Year and zw.ID = p.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '大专'  group by YEAR,ID) q on q.Year = zw.Year and zw.ID = q.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueWeiYaoQiu = '博士'  group by YEAR,ID) r on r.Year = zw.Year and zw.ID = r.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueWeiYaoQiu = '硕士'  group by YEAR,ID) s on s.Year = zw.Year and zw.ID = s.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueWeiYaoQiu = '学士'  group by YEAR,ID) t on t.Year = zw.Year and zw.ID = t.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueWeiYaoQiu = '无要求'  group by YEAR,ID) u on u.Year = zw.Year and zw.ID = u.ID
  where zw.IsLocked = 0 and zw.Status = 4", year);
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLuJiHuaDetail1Model.FillEntity(dr));
            }
            return rights;
        }

        public int InsertZhaoLuJiHuaTitle(ZhaoLuJiHuaModel ZhaoLuJiHua)
        {
            string sql = string.Format(@"INSERT INTO ZhaoLuJiHua
           ([Year]
           ,[Name]
           ,[IsLocked])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}')  ;select @@IDENTITY;", ZhaoLuJiHua.Year, ZhaoLuJiHua.Name, ZhaoLuJiHua.IsLocked);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            else return 0;
        }

        public bool DeleteZhaoLuJiHua(int id)
        {
            string sql = string.Format(@"delete from ZhaoLuJiHua where id = {0};delete from ZhaoLuJiHuaDetail where ZhaoLuJiHuaID = {0}
                                        ;delete from ZhaoLuJiHuaDetail1 where ZhaoLuJiHuaID = {0}", id);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool InsertZhaoLuJiHua(int ZhaoLuJiHuaID, ZhaoLuJiHuaDetailModel ZhaoLuJiHuaDetail, ObservableCollection<ZhaoLuJiHuaDetail1Model> ZhaoLuJiHuaDetail1)
        {
            ZhaoLuJiHuaDetail.ZhaoLuJiHuaID = ZhaoLuJiHuaID;
            string sql1 = string.Format(@"delete from ZhaoLuJiHuaDetail where ID = {32};INSERT INTO ZhaoLuJiHuaDetail
           ([ZhaoLuJiHuaID]
           ,[HDXZBZS_QJJG]
           ,[HDXZBZS_JD]
           ,[HDXZBZS_XZ]
           ,[HDXZBZS_ZF]
           ,[SYRS_QJJG]
           ,[SYRS_JD]
           ,[SYRS_XZ]
           ,[SYRS_ZF]
           ,[JYRS_QJJG]
           ,[JYRS_JD]
           ,[JYRS_XZ]
           ,[JYRS_ZF]
           ,[QBRS_QJJG]
           ,[QBRS_JD]
           ,[QBRS_XZ]
           ,[QBRS_ZF]
           ,[ZS_QJJG]
           ,[ZS_JD]
           ,[ZS_XZ]
           ,[ZS_ZF]
           ,[LiangNian]
           ,[YXCGB]
           ,[YXJWHGB]
           ,[DXSCG]
           ,[SZ]
           ,[BX],HDXZBZS_HJ,SYRS_HJ,JYRS_HJ,QBRS_HJ,ZS_HJ)
     VALUES
           ({0}
           ,{1}
           ,{2}
           ,{3}
           ,{4}
           ,{5}
           ,{6}
           ,{7}
           ,{8}
           ,{9}
           ,{10}
           ,{11}
           ,{12}
           ,{13}
           ,{14}
           ,{15}
           ,{16}
           ,{17}
           ,{18}
           ,{19}
           ,{20}
           ,{21}
           ,{22}
           ,{23}
           ,{24}
           ,{25}
           ,{26},{27}
           ,{28}
           ,{29}
           ,{30}
           ,{31})", ZhaoLuJiHuaDetail.ZhaoLuJiHuaID, ZhaoLuJiHuaDetail.HDXZBZS_QJJG, ZhaoLuJiHuaDetail.HDXZBZS_JD, ZhaoLuJiHuaDetail.HDXZBZS_XZ, ZhaoLuJiHuaDetail.HDXZBZS_ZF, ZhaoLuJiHuaDetail.SYRS_QJJG,
          ZhaoLuJiHuaDetail.SYRS_JD, ZhaoLuJiHuaDetail.SYRS_XZ, ZhaoLuJiHuaDetail.SYRS_ZF, ZhaoLuJiHuaDetail.JYRS_QJJG, ZhaoLuJiHuaDetail.JYRS_JD, ZhaoLuJiHuaDetail.JYRS_XZ,
          ZhaoLuJiHuaDetail.JYRS_ZF, ZhaoLuJiHuaDetail.QBRS_QJJG, ZhaoLuJiHuaDetail.QBRS_JD, ZhaoLuJiHuaDetail.QBRS_XZ, ZhaoLuJiHuaDetail.QBRS_ZF, ZhaoLuJiHuaDetail.ZS_QJJG,
          ZhaoLuJiHuaDetail.ZS_JD, ZhaoLuJiHuaDetail.ZS_XZ, ZhaoLuJiHuaDetail.ZS_ZF, ZhaoLuJiHuaDetail.LiangNian, ZhaoLuJiHuaDetail.YXCGB, ZhaoLuJiHuaDetail.YXJWHGB,
          ZhaoLuJiHuaDetail.DXSCG, ZhaoLuJiHuaDetail.SZ, ZhaoLuJiHuaDetail.BX,
          ZhaoLuJiHuaDetail.HDXZBZS_HJ, ZhaoLuJiHuaDetail.SYRS_HJ, ZhaoLuJiHuaDetail.JYRS_HJ, ZhaoLuJiHuaDetail.QBRS_HJ, ZhaoLuJiHuaDetail.ZS_HJ, ZhaoLuJiHuaDetail.ID);
            if (!string.IsNullOrEmpty(sql1))
                AccessHelper.GetInstance().ExecuteNonQuery(sql1);

            foreach (ZhaoLuJiHuaDetail1Model i in ZhaoLuJiHuaDetail1)
            {
                i.ZhaoLuJiHuaID = ZhaoLuJiHuaID;
                string sql2 = string.Format(@"delete from ZhaoLuJiHuaDetail1 where ID = {26};INSERT INTO ZhaoLuJiHuaDetail1
           ([ZhaoLuJiHuaID]
           ,[ZhaoLuZhiWeiID]
           ,[LiangNian]
           ,[YXCGB]
           ,[YXJWHGB]
           ,[DXSCG]
           ,[SanZhi]
           ,[BX]
           ,[ZF]
           ,[ZHGL]
           ,[XXGL]
           ,[CWGL]
           ,[JJGL]
           ,[CSJSGL]
           ,[NCJSGL]
           ,[BSYJS]
           ,[SSJYS]
           ,[SSYJS]
           ,[BKJYS]
           ,[BK]
           ,[DZJYS]
           ,[DZ]
           ,[BS]
           ,[SS]
           ,[XS]
           ,[WYQ])
     VALUES
           ({0}
           ,{1}
           ,{2}
           ,{3}
           ,{4}
           ,{5}
           ,{6}
           ,{7}
           ,{8}
           ,{9}
           ,{10}
           ,{11}
           ,{12}
           ,{13}
           ,{14}
           ,{15}
           ,{16}
           ,{17}
           ,{18}
           ,{19}
           ,{20}
           ,{21}
           ,{22}
           ,{23}
           ,{24}
           ,{25})", i.ZhaoLuJiHuaID, i.ZhaoLuZhiWeiID, i.LiangNian, i.YXCGB, i.YXJWHGB, i.DXSCG, i.SanZhi, i.BX, i.ZF, i.ZHGL, i.XXGL, i.CWGL, i.JJGL, i.CSJSGL,
         i.NCJSGL, i.BSYJS, i.SSJYS, i.SSYJS, i.BKJYS, i.BK, i.DZJYS, i.DZ, i.BS, i.SS, i.XS, i.WYQ, i.ID);
                if (!string.IsNullOrEmpty(sql2))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql2);
            }


            return true;
        }
        #endregion

        #region 导出
        private byte[] _content = null;
        public int _id;
        public FileTransferStream GetExcelFile(ZhaoLuJiHuaDetailModel model, ObservableCollection<ZhaoLuJiHuaDetail1Model> models, string username)
        {
            FileTransferStream ftfs = new FileTransferStream();
            if (_content != null)
            {
            }
            else
            {
                HSSFWorkbook workbook = null;


                MemoryStream ms = new MemoryStream(ResourceFile.录用计划表);
                workbook = new HSSFWorkbook(ms);//从流内容创建Workbook对象
                ms.Dispose();
                GetInfo(model, models, workbook);

                string strFileName = AppDomain.CurrentDomain.BaseDirectory + username + ".xls";
                if (File.Exists(strFileName))
                {
                    File.Delete(strFileName);
                }
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(fs);

                }
                using (FileStream file = File.Open(strFileName, FileMode.Open))
                {
                    BinaryReader r = new BinaryReader(file);

                    r.BaseStream.Seek(0, SeekOrigin.Begin);    //将文件指针设置到文件开

                    _content = r.ReadBytes((int)r.BaseStream.Length);
                }
                if (File.Exists(strFileName))
                {
                    File.Delete(strFileName);
                }


                //{
                //    _content = new byte[file.Length];
                //    file.Write(_content, 0, _content.Length);
                //}

                ftfs.IsFinished = true;
                ftfs.FileContent = _content;

            }
            return ftfs;
        }
        private DataSet GetInfo(ZhaoLuJiHuaDetailModel model, ObservableCollection<ZhaoLuJiHuaDetail1Model> models, NPOI.HSSF.UserModel.HSSFWorkbook workbook)
        {
            HSSFSheet sheet1 = workbook.GetSheetAt(0);
            #region 上面的

            SetCellValue(sheet1, 7, 1, model.HDXZBZS_HJ.ToString());
            SetCellValue(sheet1, 7, 2, model.HDXZBZS_QJJG.ToString());
            SetCellValue(sheet1, 7, 3, model.HDXZBZS_JD.ToString());
            SetCellValue(sheet1, 7, 4, model.HDXZBZS_XZ.ToString());
            SetCellValue(sheet1, 7, 5, model.HDXZBZS_ZF.ToString());
            SetCellValue(sheet1, 7, 10, model.SYRS_HJ.ToString());
            SetCellValue(sheet1, 7, 11, model.SYRS_QJJG.ToString());
            SetCellValue(sheet1, 7, 12, model.SYRS_JD.ToString());
            SetCellValue(sheet1, 7, 13, model.SYRS_XZ.ToString());

            SetCellValue(sheet1, 7,14, model.SYRS_ZF.ToString());
            SetCellValue(sheet1, 7, 19, model.JYRS_HJ.ToString());
            SetCellValue(sheet1, 7, 20, model.JYRS_QJJG.ToString());
            SetCellValue(sheet1, 7, 21, model.JYRS_JD.ToString());
            SetCellValue(sheet1, 7, 22, model.JYRS_XZ.ToString());
            SetCellValue(sheet1, 7, 23, model.JYRS_ZF.ToString());
            SetCellValue(sheet1, 7, 28, model.QBRS_HJ.ToString());

            SetCellValue(sheet1, 7, 29, model.QBRS_QJJG.ToString());
            SetCellValue(sheet1, 7, 30, model.QBRS_JD.ToString());
            SetCellValue(sheet1, 7, 31, model.QBRS_XZ.ToString());
            SetCellValue(sheet1, 7, 32, model.QBRS_ZF.ToString());
            SetCellValue(sheet1, 7, 37, model.ZS_HJ.ToString());
            SetCellValue(sheet1, 7, 38, model.ZS_QJJG.ToString());
            SetCellValue(sheet1, 7, 39, model.ZS_JD.ToString());
            SetCellValue(sheet1, 7, 40, model.ZS_XZ.ToString());
            SetCellValue(sheet1, 7, 41, model.ZS_ZF.ToString());
            SetCellValue(sheet1, 7, 46, model.LiangNian.ToString());
            SetCellValue(sheet1, 7, 48, model.YXCGB.ToString());

            SetCellValue(sheet1, 7, 49, model.YXJWHGB.ToString());
            SetCellValue(sheet1, 7, 50, model.DXSCG.ToString());
            SetCellValue(sheet1, 7, 51, model.SZ.ToString());
            SetCellValue(sheet1, 7, 52, model.BX.ToString());
            #endregion
            #region 下面的
            HSSFCellStyle style = workbook.CreateCellStyle();
            style.Alignment = HSSFCellStyle.ALIGN_CENTER;
            style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
            style.BorderBottom = HSSFCellStyle.BORDER_THIN;
            style.BorderLeft = HSSFCellStyle.BORDER_THIN;
            style.BorderRight = HSSFCellStyle.BORDER_THIN;
            style.BorderTop = HSSFCellStyle.BORDER_THIN;
            for (int i = 0; i < models.Count; i++)
            {
                var row = sheet1.CreateRow(i + 9);
                row.Height = 20*20;
                for(int ii = 1;ii<53;ii++)
                {
                    HSSFCell cell = row.CreateCell(ii);
                    cell.CellStyle = style;
                }
                sheet1.AddMergedRegion(new Region(i + 9, 2, i + 9, 10));
                sheet1.AddMergedRegion(new Region(i + 9, 11, i + 9, 12));
                sheet1.AddMergedRegion(new Region(i + 9, 13, i + 9, 14));
                sheet1.AddMergedRegion(new Region(i + 9, 15, i + 9, 16));
                sheet1.AddMergedRegion(new Region(i + 9, 17, i + 9, 18));
                sheet1.AddMergedRegion(new Region(i + 9, 20, i + 9, 21));
                sheet1.AddMergedRegion(new Region(i + 9, 22, i + 9, 23));
                sheet1.AddMergedRegion(new Region(i + 9, 24, i + 9, 25));
                sheet1.AddMergedRegion(new Region(i + 9, 26, i + 9, 27));
                sheet1.AddMergedRegion(new Region(i + 9, 28, i + 9, 29));
                sheet1.AddMergedRegion(new Region(i + 9, 30, i + 9, 31));
                sheet1.AddMergedRegion(new Region(i + 9, 32, i + 9, 33));
                sheet1.AddMergedRegion(new Region(i + 9, 34, i + 9, 36));
                sheet1.AddMergedRegion(new Region(i + 9, 38, i + 9, 39));
                sheet1.AddMergedRegion(new Region(i + 9, 40, i + 9, 41));
                sheet1.AddMergedRegion(new Region(i + 9, 42, i + 9, 43));

                sheet1.AddMergedRegion(new Region(i + 9, 44, i + 9, 45));
                sheet1.AddMergedRegion(new Region(i + 9, 50, i + 9, 52));
                SetCellValue(sheet1, i + 10, 1, models[i].OrgName_Duty.ToString());
                SetCellValue(sheet1, i + 10, 2, models[i].LiangNian.ToString());
                SetCellValue(sheet1, i + 10, 11, models[i].YXCGB.ToString());
                SetCellValue(sheet1, i + 10, 13, models[i].YXJWHGB.ToString());
                SetCellValue(sheet1, i + 10, 15, models[i].DXSCG.ToString());
                SetCellValue(sheet1, i + 10, 17, models[i].SanZhi.ToString());
                SetCellValue(sheet1, i + 10, 19, models[i].BX.ToString());
                SetCellValue(sheet1, i + 10, 20, models[i].ZF.ToString());
                SetCellValue(sheet1, i + 10, 22, models[i].ZHGL.ToString());

                SetCellValue(sheet1, i + 10, 24, models[i].XXGL.ToString());
                SetCellValue(sheet1, i + 10, 26, models[i].CWGL.ToString());
                SetCellValue(sheet1, i + 10, 28, models[i].JJGL.ToString());
                SetCellValue(sheet1, i + 10, 30, models[i].CSJSGL.ToString());
                SetCellValue(sheet1, i + 10, 32, models[i].NCJSGL.ToString());
                SetCellValue(sheet1, i + 10, 34, models[i].BSYJS.ToString());
                SetCellValue(sheet1, i + 10, 37, models[i].SSJYS.ToString());

                SetCellValue(sheet1, i + 10, 38, models[i].SSYJS.ToString());
                SetCellValue(sheet1, i + 10, 40, models[i].BKJYS.ToString());
                SetCellValue(sheet1, i + 10, 42, models[i].BK.ToString());
                SetCellValue(sheet1, i + 10, 44, models[i].DZJYS.ToString());
                SetCellValue(sheet1, i + 10, 46, models[i].DZ.ToString());
                SetCellValue(sheet1, i + 10, 47, models[i].BS.ToString());
                SetCellValue(sheet1, i + 10, 48, models[i].SS.ToString());
                SetCellValue(sheet1, i + 10, 49, models[i].XS.ToString());
                SetCellValue(sheet1, i + 10, 50, models[i].WYQ.ToString());
            }
            
            
            #endregion
            return null;
        }
        private bool SetCellValue(NPOI.HSSF.UserModel.HSSFCell cell, object value)
        {
            if (value != null)
            {
                cell.SetCellValue(value.ToString());
            }
            return true;
        }
        private bool SetCellValue(NPOI.HSSF.UserModel.HSSFSheet sheet, int row, int column, string dr)
        {
            HSSFRow hrow = sheet.GetRow(row - 1);
            if (hrow != null)
            {
                HSSFCell cell = hrow.GetCell(column);
                if (cell != null)
                {
                    SetCellValue(cell, dr);
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 导入数据前判断是否存在错误数据
        /// </summary>
        /// <param name="InEmpBasicModels"></param>
        /// <returns></returns>
        public bool CanDaoRuAll(int year, int month)
        {
            string sql = string.Format(@"select count(ID) Cou  from ZhaoLu_DaoRuKaoSheng where year = {0} and month={1}", year,month);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0 )
            {
                if(Convert.ToInt32(ds.Tables[0].Rows[0][0]) > 0) return false;
                else return true;
            }
            else return true;
        }

        public ObservableCollection<ZhaoLu_DaoRuKaoShengModel> GetZhaoLu_DaoRuKaoShengModel(int year,bool isfangqi,int month,int id)
        {
            ObservableCollection<ZhaoLu_DaoRuKaoShengModel> rights = new ObservableCollection<ZhaoLu_DaoRuKaoShengModel>();
            string sql = "";
            if(id == 0)
            {
                if (isfangqi == false)
                    sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY YRBM,BBZW,TotalScore ) Sec,*,'false' as IsSelected,0 as MingCi from ZhaoLu_DaoRuKaoSheng where year = {0} and month={1} and IsFangQi = 0 order by YRBM,BBZW,TotalScore desc", year, month);
                else
                    sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY YRBM,BBZW,TotalScore) Sec,*,'false' as IsSelected,0 as MingCi from ZhaoLu_DaoRuKaoSheng where year = {0} and month={1} and IsFangQi = 1 order by YRBM,BBZW,TotalScore desc", year, month);
            }
            else sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY YRBM,BBZW,TotalScore ) Sec,*,'false' as IsSelected,0 as MingCi from ZhaoLu_DaoRuKaoSheng where id = {0}", id);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLu_DaoRuKaoShengModel.FillEntity(dr));
            }
            return rights;
        }
        public bool ShanChongFu(int year, int month)
        {
            string sql = "delete from ZhaoLu_DaoRuKaoSheng where year =" + year + " and month=" + month;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DaoRuZhaoLuKaoSheng(ZhaoLu_DaoRuKaoShengModel model)
        {
            string sql = "";
            //if(isfugai == "是")
            //{
            //    sql = "delete from ZhaoLu_DaoRuKaoSheng where year =" + models[0].Year ;
            //    if (!string.IsNullOrEmpty(sql))
            //        AccessHelper.GetInstance().ExecuteNonQuery(sql);
            //}
            //foreach(ZhaoLu_DaoRuKaoShengModel model in models)
            //{
                sql =  string.Format(@"INSERT INTO ZhaoLu_DaoRuKaoSheng
           ([Year]
           ,[ZCBH]
           ,[XingMing]
           ,[SFZH]
           ,[ZWID]
           ,[ZLDW]
           ,[BBZW]
           ,[ZKKM]
           ,[SL]
           ,[XC]
           ,[ZF]
           ,[ZongFen]
           ,[XB]
           ,[CSRQ]
           ,[JKCD]
           ,[SG]
           ,[TZ]
           ,[MZ]
           ,[HYZK]
           ,[POXL]
           ,[KSSF]
           ,[XL]
           ,[ZZMM]
           ,[XW]
           ,[BKBYYX]
           ,[BKZY]
           ,[BKBYSJ]
           ,[SSYX]
           ,[SSZY]
           ,[SSBYSJ]
           ,[BSYX]
           ,[BSZY]
           ,[BSBYSJ]
           ,[GRJL]
           ,[TC]
           ,[SXZYKC]
           ,[WY1]
           ,[WY2]
           ,[WYDJ1]
           ,[WYDJ2]
           ,[JSJSP]
           ,[TXDZ]
           ,[LXFS]
           ,[SJHM]
           ,[GZDW]
           ,[GZNX]
           ,[BYSJB]
           ,[SY]
           ,[HJ]
           ,[YB]
           ,[AddDay],YRBM,month,isfangqi)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,'{14}'
           ,'{15}'
           ,'{16}'
           ,'{17}'
           ,'{18}'
           ,'{19}'
           ,'{20}'
           ,'{21}'
           ,'{22}'
           ,'{23}'
           ,'{24}'
           ,'{25}'
           ,'{26}'
           ,'{27}'
           ,'{28}'
           ,'{29}'
           ,'{30}'
           ,'{31}'
           ,'{32}'
           ,'{33}'
           ,'{34}'
           ,'{35}'
           ,'{36}'
           ,'{37}'
           ,'{38}'
           ,'{39}'
           ,'{40}'
           ,'{41}'
           ,'{42}'
           ,'{43}'
           ,'{44}'
           ,'{45}'
           ,'{46}'
           ,'{47}'
           ,'{48}'
           ,'{49}'
           ,'{50}','{51}',{52},'{53}')", model.Year
           , model.ZCBH
           , model.XingMing
           , model.SFZH
           , model.ZWID
           , model.ZLDW
           , model.BBZW
           , model.ZKKM
           , model.SL
           , model.XC
           , model.ZF
           , model.ZongFen
           , model.XB
           , model.CSRQ
           , model.JKCD
           , model.SG
           , model.TZ
           , model.MZ
           , model.HYZK
           , model.POXL
           , model.KSSF
           , model.XL
           , model.ZZMM
           , model.XW
           , model.BKBYYX
           , model.BKZY
           , model.BKBYSJ
           , model.SSYX
           , model.SSZY
           , model.SSBYSJ
           , model.BSYX
           , model.BSZY
           , model.BSBYSJ
           , model.GRJL
           , model.TC
           , model.SXZYKC
           , model.WY1
           , model.WY2
           , model.WYDJ1
           , model.WYDJ2
           , model.JSJSP
           , model.TXDZ
           , model.LXFS
           , model.SJHM
           , model.GZDW
           , model.GZNX
           , model.BYSJB
           , model.SY
           , model.HJ
           , model.YB
           , model.AddDay,model.YRBM,model.Month,model.IsFangQi);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                
            //}
            return true;
        }

        #endregion

        public ObservableCollection<ZL_ZhiWeiShiJianAnPaiModel> ZL_PublicOfficialSelectionForAnPai(int year)
        {
            ObservableCollection<ZL_ZhiWeiShiJianAnPaiModel> rights = new ObservableCollection<ZL_ZhiWeiShiJianAnPaiModel>();
            string sql = @"select ROW_NUMBER() OVER(ORDER BY zw.ID) Sec, zw.* from 
(select b.* from
(select distinct top 100 percent (YRBM + '-' + BBZW) as u  from ZhaoLu_DaoRuKaoSheng where isfangqi = 0 and Year = " + year + @") a inner join
(select (o.Name + '-' + zzw.Duty) u1,zzw.* from ZhaoLuZhiWei zzw inner join OrgForHR o on zzw.OrgID = o.ID where IsLocked = 0 and Status = 4 and Year = " + year + @") b
on a.u = b.u1) zzw 
left join ZL_ZhiWeiShiJianAnPai zw on zzw.ID = zw.PublicOfficialSelectionID
where zzw.Year = " + year + " and  zzw.IsLocked = 0 and zzw.Status = 4 order by RiQi,Sec1 ,EndShi  ";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZL_ZhiWeiShiJianAnPaiModel.FillEntity(dr));
            }
            return rights;
        }

        public ObservableCollection<ZL_ZhiWeiShiJianAnPaiModel> ZL_PublicOfficialSelectionForAnPaiSuoDing(int year)
        {
            ObservableCollection<ZL_ZhiWeiShiJianAnPaiModel> rights = new ObservableCollection<ZL_ZhiWeiShiJianAnPaiModel>();
            string sql = "";
            //            sql = @"select ROW_NUMBER() OVER(ORDER BY zw.ID) Sec, zw.RiQi ,zw.Zu,pos.Duty,'' ZhuKaoGuanS,'' ZhuanYeKaoGuan1S,
            //'' ZhuanYeKaoGuan2S,'' DanWeiKaoGuan1S,zw.ID,
            //'' DanWeiKaoGuan2S,'' MianShiJianDuYuan,'' GongZuoRenGuan1,'' GongZuoRenGuan2,'' Shi,'' RenShu,'' DiDian from PublicOfficialSelection pos  left join ZhiWeiShiJianAnPai zw on pos.ID = zw.PublicOfficialSelectionID
            //where  pos.IsLocked = 1 and Status = 4 and pos.Year = " + year + " and pos.FaBuType = '" + tiaojian + "'";
            sql = @"select ROW_NUMBER() OVER(ORDER BY zw.ID) Sec, zw.RiQi ,zw.Zu,pos.Duty,k.Name ZhuKaoGuanS,kk.Name ZhuanYeKaoGuan1S,
kkk.Name ZhuanYeKaoGuan2S,kkkk.Name DanWeiKaoGuan1S,zw.ID,
kkkkk.Name DanWeiKaoGuan2S,zw.MianShiJianDuYuan,zw.GongZuoRenGuan1,zw.GongZuoRenGuan2,zw.StartShi,zw.EndShi,
zw.RenShu,zw.DiDian 
from PublicOfficialSelection pos  
left join ZL_ZhiWeiShiJianAnPai zw on pos.ID = zw.PublicOfficialSelectionID
left join KaoGuan k on k.ID = zw.ZhuKaoGuan
left join KaoGuan kk on kk.ID = zw.ZhuanYeKaoGuan1
left join KaoGuan kkk on kkk.ID = zw.ZhuanYeKaoGuan2
left join KaoGuan kkkk on kkkk.ID = zw.DanWeiKaoGuan1
left join KaoGuan kkkkk on kkkkk.ID = zw.DanWeiKaoGuan2
where  Status = 4 and pos.Year = " + year + " and  pos.IsLocked = 1 and zw.isfangqi = 0";
            //if (tiaojian == "竞争性选拔")
            //{
            //    sql += " and IsJZXXB  = 1";
            //}
            //else
            //{
            //    sql += " and IsXDJL  = 1";
            //}
            sql += " order by Sec";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZL_ZhiWeiShiJianAnPaiModel.FillEntity1(dr));
            }
            return rights;
        }

        public Dictionary<int, int> GetZL_MianShiRenShu(int PublicOfficialSelectionID, int year)
        {
            Dictionary<int, int> dic = new Dictionary<int, int>();
            string sql =
                @"select c.cou,b.ID from
(select distinct top 100 percent (YRBM + '-' + BBZW) as u  from ZhaoLu_DaoRuKaoSheng where isfangqi = 0 and YEAR = " + year + @") a inner join
(select (o.Name + '-' + zzw.Duty) u1,zzw.* from ZhaoLuZhiWei zzw inner join OrgForHR o on zzw.OrgID = o.ID where Status = 4 and IsLocked = 0 and Year = " + year + @") b
on a.u = b.u1
inner join
(select COUNT(*) Cou,(YRBM + '-' + BBZW) as u from ZhaoLu_DaoRuKaoSheng where isfangqi = 0 and YEAR = " + year + @" group by (YRBM + '-' + BBZW)) c 
on c.u = a.u ";
            if (PublicOfficialSelectionID != 0)
                sql += " and b.ID =" + PublicOfficialSelectionID;
            //sql += " group by zzw.ID";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                dic.Add(Convert.ToInt32(dr[1]), Convert.ToInt32(dr[0]));
            }
            return dic;
        }

        public ObservableCollection<ZhaoLuZhiWeiModel> ZL_GetPublicOfficialSelectionForDaoChu(int year, string islocked)
        {
            ObservableCollection<ZhaoLuZhiWeiModel> rights = new ObservableCollection<ZhaoLuZhiWeiModel>();
            string sql = "";
            sql =
                @"select distinct zzw.ID,zzw.*,o.Name OrgName from 
((select b.* from
(select distinct top 100 percent (YRBM + '-' + BBZW) as u  from ZhaoLu_DaoRuKaoSheng where isfangqi = 0 and Year = " + year + @") a inner join
(select (o.Name + '-' + zzw.Duty) u1,zzw.* from ZhaoLuZhiWei zzw inner join OrgForHR o on zzw.OrgID = o.ID where Status = 4 and IsLocked = '" + islocked + "' and Year = " + year + @") b
on a.u = b.u1) ) zzw inner join OrgForHR o on o.ID = zzw.OrgID
inner join ZhaoLu_DaoRuKaoSheng zld on zld.YRBM = o.Name where zzw.Status = 4 and  zzw.year = " + year + " and zld.Year =" + year;
            sql += " and zzw.IsLocked = '" + islocked + "'";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLuZhiWeiModel.FillEntity(dr));
            }
            return rights;
        }

        public bool ZL_InsertZu(ZL_ZhiWeiShiJianAnPaiModel model, string type)
        {
            switch (model.Zu)
            {
                case "第一组":
                    model.Sec1 = 1;
                    break;
                case "第二组":
                    model.Sec1 = 2;
                    break;
                case "第三组":
                    model.Sec1 = 3;
                    break;
                case "第四组":
                    model.Sec1 = 4;
                    break;
                case "第五组":
                    model.Sec1 = 5;
                    break;
                case "第六组":
                    model.Sec1 = 6;
                    break;
                case "第七组":
                    model.Sec1 = 7;
                    break;
                case "第八组":
                    model.Sec1 = 8;
                    break;
                case "第九组":
                    model.Sec1 = 9;
                    break;
            }
            string sql = string.Format(@"delete from ZL_ZhiWeiShiJianAnPai where PublicOfficialSelectionID = {0} and type = '{17}'; INSERT INTO ZL_ZhiWeiShiJianAnPai
           ([PublicOfficialSelectionID]
           ,[Year]
           ,[Zu]
           ,[RiQi]
           ,[ZhuKaoGuan]
           ,[ZhuanYeKaoGuan1]
           ,[ZhuanYeKaoGuan2]
           ,[DanWeiKaoGuan1]
           ,[DanWeiKaoGuan2]
           ,[MianShiJianDuYuan]
           ,[GongZuoRenGuan1]
           ,[GongZuoRenGuan2]
           ,[StartShi]
           ,[RenShu]
           ,[DiDian]
           ,[EndShi],Sec1,type)
     VALUES
           ({0}
           ,{1}
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,'{14}'
           ,'{15}','{16}','{17}')", model.PublicOfficialSelectionID, model.Year,
                    model.Zu,
                    model.RiQi, model.ZhuKaoGuan, model.ZhuanYeKaoGuan1, model.ZhuanYeKaoGuan2, model.DanWeiKaoGuan1
                    , model.DanWeiKaoGuan2, model.MianShiJianDuYuan, model.GongZuoRenGuan1, model.GongZuoRenGuan2, model.StartShi,
                    model.RenShu, model.DiDian, model.EndShi, model.Sec1, type);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool QiQuan(string ID,string huanjie)
        {
            string sql = "update ZhaoLu_DaoRuKaoSheng set IsFangQi = 1,FangQiHuanJie = '" + huanjie + "' where ID in (" + ID + ")";
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool UpdateZL_MianShiFenShu(ZhaoLu_DaoRuKaoShengModel model)
        {
            string sql = "update ZhaoLu_DaoRuKaoSheng set MianShiScore = " + model.MianShiScore.ToString() + ",TotalScore = " + model.TotalScore.ToString() + " where ID =" + model.ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateZL_TiJian(ZhaoLu_DaoRuKaoShengModel model)
        {
            string sql = "";
            if(!string.IsNullOrEmpty(model.TiJianResult))
            {
                sql = string.Format("update ZhaoLu_DaoRuKaoSheng set TiJianResult = '{0}' where ID ={1}",model.TiJianResult,model.ID);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            return true;
        }
        public bool UpdateZL_ZhengShen(ZhaoLu_DaoRuKaoShengModel model)
        {
            string sql = "";
            if (!string.IsNullOrEmpty(model.ZhengShenResult))
            {
                sql = string.Format("update ZhaoLu_DaoRuKaoSheng set ZhengShenResult = '{0}' where ID ={1}", model.ZhengShenResult, model.ID);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            return true;
        }
        /// <summary>
        /// 拟同意或放弃
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool NiTongYiorNot_ZL(List<int> PublicOfficialRecordID, string istongyi)
        {
            foreach (int id in PublicOfficialRecordID)
            {
                string sql = string.Format(@"update ZhaoLu_DaoRuKaoSheng set IsTongYi = '{1}' where ID = {0}", id, istongyi);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            return true;
        }

        public void InsertEmp(int PublicOfficialRecordID)
        {
            string sql = string.Format(@"insert into dbo.Emp_BasicInfo
(Name,CardNo,OrgID,PositionName,Gender,Birthday,EmpStatus,Health,Native,Marriage,Political,NativePlace)
select XingMing,SFZH,o.id OrgID,YRBM,XB,CSRQ,'在职',JKCD,MZ,HYZK,ZZMM,HJ from dbo.ZhaoLu_DaoRuKaoSheng zl
inner join orgforhr o on zl.ZLDW = o.Name
where zl.id = {0} ;SELECT @@IDENTITY", PublicOfficialRecordID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if(!string.IsNullOrEmpty(ds.Tables[0].Rows[0][0].ToString()))
            {
                int EmpID = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                string degreesql = string.Format(@"insert into dbo.Emp_Degree
(empbasicid,degreename)
select {0}, XW from dbo.ZhaoLu_DaoRuKaoSheng where id = {1}", EmpID, PublicOfficialRecordID);
                string dutysql = string.Format(@"insert into dbo.Emp_Duty
(empbasicid,dutyname,okdate)
select {0}, BBZW,getdate() from dbo.ZhaoLu_DaoRuKaoSheng where id = {1}", EmpID, PublicOfficialRecordID);
                string resumesql = string.Format(@"insert into dbo.Emp_Resume
(empbasicid,BeginDate,Unit,Duty)
select {0}, getdate(),ZLDW,BBZW from dbo.ZhaoLu_DaoRuKaoSheng where id = {1}", EmpID, PublicOfficialRecordID);

                if (!string.IsNullOrEmpty(degreesql) && !string.IsNullOrEmpty(dutysql) && !string.IsNullOrEmpty(resumesql))
                {
                    AccessHelper.GetInstance().ExecuteNonQuery(degreesql);
                    AccessHelper.GetInstance().ExecuteNonQuery(dutysql);
                    AccessHelper.GetInstance().ExecuteNonQuery(resumesql);
                }
            }
            
        }
    }
}
